﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using QuanLyBanHang.DataConnection;
using System.Windows.Forms;

namespace QuanLyBanHang.QuerySQL
{
    class ClassSanpham
    {
        Connect conn = new Connect();
        //
        public DataTable ShowSP()
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from SANPHAM";
                dt = conn.getTable(sql);
            }
            catch(Exception e) {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return dt;
        }
        public DataTable ShowLSP()
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from LOAISP";
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: " +e.Message);
            }
            return dt;
        }
        public DataTable ShowNSX()
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from NHASX";
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: "+e.Message);
            }
            return dt;
        }
        //
        public DataTable Timkiem_Masp(string masp)
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from SANPHAM where MASP like '%" + masp + "%'";
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: "+e.Message);
            }
            return dt;
        }
        public DataTable Timkiem_tenSP(string tensp)
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from SANPHAM where TENSP like N'%" + tensp + "%'";
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: "+e.Message);
            }
            return dt;
        }
        public DataTable Timkiem_ML(string ML)
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from SANPHAM where ML like '%" + ML + "%'";
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: "+e.Message);
            }
            return dt;
        }
        public DataTable Timkiem_NSX(string NSX)
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from SANPHAM where MANSX like '%" + NSX + "%'";                
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: "+e.Message);
            }
            return dt;
        }
        public DataTable Timkiem(string tensp, string ml, string nsx, double gia1, double gia2)
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "select *from SANPHAM where TENSP = N'" + tensp + "' and ML = '" + ml + "' and MANSX=  '" + nsx + "' and GIABAN > " + gia1 + " and GIABAN < " + gia2 + "";                
                dt = conn.getTable(sql);
            }catch(Exception e){
                MessageBox.Show("Loi: "+ e.Message);
            }
            return dt;
        }
        //them sua xoa
        public void ThemSP(string masp, string tensp, string ml, string nsx, string dvt, double gm, double gb, int sl)
        {
            try
            {
                string sql = "insert into SANPHAM values ('" + masp + "',N'" + tensp + "','" + ml + "','" + nsx + "','" + dvt + "'," + gm + ","+gb+"," + sl + " )";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Thêm thành công!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi : " + e.Message);
            }

        }
        public void SuaSP(string masp, string tensp, string ml, string nsx, string dvt, double gm, double gb, int sl)
        {
            try
            {
                string sql = "update SANPHAM set TENSP = N'" + tensp + "',ML ='" + ml + "',MANSX ='" + nsx + "',DVT ='" + dvt + "',GIAMUA = "+gm+", GIABAN = "+gb+", SOLUONG = "+sl+" where MASP = '" + masp + "' ";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Sửa thành công!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi : " + e.Message);
            }
            
        }
        public void XoaSP(string masp)
        {
            try
            {
                string sql = "delete SANPHAM where MASP = '" + masp + "'";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Xóa thành công !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
    }
}
